"""
飞检数据STEP8筛选 - clue_bad_x.py
============================================================================

将王总的 x 规则一个个跑一遍

============================================================================
"""



import os
import re
import sys
import time
import pandas as pd
from sqlalchemy import text
import json

sys.path.append(os.path.abspath(os.path.join(os.path.dirname(__file__), '..')))
from config import create_db_engine

from sqlalchemy.engine import Engine

# 定义 x 函数列表
X_LIST = [
    {'sql_file': 'x1.sql', 'output_filename': 'X1_全院按次手术数量情况_内部'},
    {'sql_file': 'x2.sql', 'output_filename': 'X2_患者维度按次手术数量情况_内部'},
    {'sql_file': 'x3.sql', 'output_filename': 'X3_检验检查费占整单费用比不小于0.8_内部'},
    {'sql_file': 'x4.sql', 'output_filename': 'X4_药费占整单费用比不小于0.8_内部'},
    {'sql_file': 'x5.sql', 'output_filename': 'X5_二次范媛间隔时间不大于5天_内部'},
    {'sql_file': 'x6.sql', 'output_filename': 'X6_单人日均血透大于0.43次_内部'},
    {'sql_file': 'x7.sql', 'output_filename': 'X7_透析管路数量大于操作数量_内部'},
    {'sql_file': 'x8.sql', 'output_filename': 'X8_透析器数量大于操作数量_内部'},
]

_ILLEGAL_CHAR_RE = re.compile(r"[\x00-\x08\x0b\x0c\x0e-\x1f]")

def clean_illegal_chars(s: str) -> str:
    """Remove illegal characters from a string."""
    if pd.isna(s):
        return s
    return _ILLEGAL_CHAR_RE.sub("", s)

# 时间函数
t0 = time.time()
def elapsed() -> str:
    timeStr = time.strftime("%H:%M:%S", time.localtime())
    delta = int(time.time() - t0)
    if delta < 60:
        return f"{timeStr} (+ {delta} sec)"
    elif delta < 3600:
        m, s = divmod(delta, 60)
        return f"{timeStr} (+ {m} min {s} sec)"
    elif delta < 86400:
        h, rem = divmod(delta, 3600)
        m, s = divmod(rem, 60)
        return f"{timeStr} (+ {h} hour {m} min {s} sec)"
    else:
        d, rem = divmod(delta, 86400)
        h, rem = divmod(rem, 3600)
        m, s = divmod(rem, 60)
        return f"{timeStr} (+ {d} day {h} hour {m} min {s} sec)"
    
# 创建数据库引擎
engine = create_db_engine()

def load_hsp_list(json_path: str) -> list:
    """Load hospital list from a JSON file."""
    with open(json_path, 'r', encoding='utf-8') as f:
        hsp_list = json.load(f)
    return hsp_list

# apply_clue
def apply_clue(engine: Engine, hsp_abbr: str, sql_file: str, output_filename: str) -> pd.DataFrame:
    # 从 SQL 文件读取 SQL 查询
    sql_path = os.path.join('clue866_library', sql_file)
    with open(sql_path, 'r', encoding='utf-8') as f:
        sql = text(f.read())
    with engine.connect() as conn:
        df = pd.read_sql(sql, conn, params={'hsp_abbr': hsp_abbr})
    # 如果 df 为空，直接返回
    if df.empty:
        return df
    # 判断目录是否存在，不存在则创建
    output_dir_pre = os.path.join(os.path.dirname(__file__), 'clue')
    os.makedirs(output_dir_pre, exist_ok=True)
    os.makedirs(os.path.join(output_dir_pre, hsp_abbr), exist_ok=True)
    # 将df保存到 Excel 文件，保存在 STEP8筛选/clue/{output_filename}_{hsp_abbr}.xlsx
    output_path = os.path.join(output_dir_pre, hsp_abbr, f'clue866_{output_filename}_{hsp_abbr}.xlsx')
    df.to_excel(output_path, index=False)
    return df

# X1：X1_全院按次手术数量情况.sql
# X2：X2_患者维度按次手术数量情况.sql


# 主程序
if __name__ == "__main__":
    hsp_list = load_hsp_list('hspList.json')
    for hsp in hsp_list:

        hsp_abbr = hsp['hsp_abbr']
        print(f"{elapsed()} - 处理医院：{hsp_abbr}")

        for x in X_LIST:
            sql_file = x['sql_file']

            output_filename = x['output_filename']
            print(f"{elapsed()} -  处理规则文件：{sql_file}")

            df_result = apply_clue(engine, hsp_abbr, sql_file, output_filename)
            if df_result.empty:
                print(f"{elapsed()} -   结果为空。")
            else:
                print(f"{elapsed()} -   结果行数：{len(df_result)}，已保存到文件：{output_filename}_{hsp_abbr}.xlsx")
    
    print(f"{elapsed()} - 所有规则处理完成。")

